#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2024/12/18 10:29
# @Author : ZhengQL

import openpyxl
from common.Path_Handler import PathHandler

class ExcelHandler:
    def __init__(self, file_path):
        self.file_path = file_path

    def open_excel(self, sheet_name):
        wb = openpyxl.load_workbook(self.file_path)
        # 获取指定sheet页
        sheet = wb[sheet_name]
        return sheet

    def get_headers(self, sheet_name):
        wb = self.open_excel(sheet_name)
        headers = []
        for i in wb[1]:
            headers.append(i.value)
            # print(i.value)
        return headers


    def read_excel(self, sheet_name):
        sheet = self.open_excel(sheet_name)
        rows = list(sheet.rows)
        data = []
        for row in rows[1:]:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
                # 将两个列表合成字典
                data_dict = dict(zip(self.get_headers(sheet_name), row_data))
            data.append(data_dict)
            # print(data_dict)
        return data

    def write_excel(self, file_path, sheet_name, row, cloumn, data):
        wb = openpyxl.load_workbook(file_path)
        sheet = wb[sheet_name]
        sheet.cell(row=row, column=cloumn).value = data
        wb.save(file_path)
        wb.close()



if __name__ == '__main__':
    excel = ExcelHandler(PathHandler.cases_path + r'/cases.xlsx')
    data = excel.read_excel('Sheet1')
    print(data)

    data2 = excel.write_excel(PathHandler.cases_path + r'/cases.xlsx','Sheet2', 3, 2, 'hello123')
